package studentportal;

import java.sql.*; // JDBC stuff.
import java.io.*;  // Reading user input.

import oracle.net.ns.RedirectPacket;

public class StudentPortal
{
	/* This is the driving engine of the program. It parses the
	 * command-line arguments and calls the appropriate methods in
	 * the other classes.
	 *
	 * You should edit this file in two ways:
	 * 	1) 	Insert your database username and password (no @medic1!)
	 *		in the proper places.
	 *	2)	Implement the three functions getInformation, registerStudent
	 *		and unregisterStudent.
	 */
	public static void main(String[] args)
	{
		if (args.length == 1) {
			try {
				DriverManager.registerDriver(new oracle.jdbc.OracleDriver());
				String url = "jdbc:oracle:thin:@tycho.ita.chalmers.se:1521/kingu.ita.chalmers.se";
				String userName = "vtda357_017"; // Your username goes here!
				String password = "vtda357_017"; // Your password goes here!
				Connection conn = DriverManager.getConnection(url,userName,password);

				String student = args[0]; // This is the identifier for the student.
				BufferedReader input = new BufferedReader(new InputStreamReader(System.in));
				System.out.println("Welcome!");
				while(true) {
					System.out.println("Please choose a mode of operation:");
					System.out.print("? > ");
					String mode = input.readLine();
					if ((new String("information")).startsWith(mode.toLowerCase())) {
						/* Information mode */
						getInformation(conn, student);
					} else if ((new String("register")).startsWith(mode.toLowerCase())) {
						/* Register student mode */
						System.out.print("Register for what course? > ");
						String course = input.readLine();
						registerStudent(conn, student, course);
					} else if ((new String("unregister")).startsWith(mode.toLowerCase())) {
						/* Unregister student mode */
						System.out.print("Unregister from what course? > ");
						String course = input.readLine();
						unregisterStudent(conn, student, course);
					} else if ((new String("quit")).startsWith(mode.toLowerCase())) {
						System.out.println("Goodbye!");
						break;
					} else {
						System.out.println("Unknown argument, please choose either information, register, unregister or quit!");
						continue;
					}
				}
				conn.close();
			} catch (SQLException e) {
				System.err.println(e);
				System.exit(2);
			} catch (IOException e) {
				System.err.println(e);
				System.exit(2);
			}
		} else {
			System.err.println("Wrong number of arguments");
			System.exit(3);
		}
	}

	static void getInformation(Connection conn, String student)
	{
		try {
			// Statements
			Statement statement = conn.createStatement();
			Statement lineBranchStatement = conn.createStatement();
			Statement readCoursesStatement = conn.createStatement();
			Statement registeredCoursesStatement = conn.createStatement();

			// ResultSets
			ResultSet queryResultSet = statement.executeQuery("SELECT * FROM PathToGraduation WHERE (id = " + student + ")");
			ResultSet lineBranchQuery = lineBranchStatement.executeQuery("SELECT * FROM StudentsFollowing WHERE (id = " + student + ")");
			ResultSet readCoursesQuery = readCoursesStatement.executeQuery("SELECT * FROM FinishedCourses  JOIN (SELECT * FROM Courses) ON course=code WHERE (id = " + student +")");
			ResultSet registeredCoursesQuery = registeredCoursesStatement.executeQuery("SELECT * FROM Registrations NATURAL JOIN Courses WHERE (id = " + student + ")");

			while (queryResultSet.next()) {
				System.out.println("Information for student " + student);
				System.out.println("-------------------------------------");
				System.out.println("Name: " + queryResultSet.getObject("name"));

				// New query to get line and branch
				while(lineBranchQuery.next()) {
					System.out.println("Line: " + lineBranchQuery.getObject("programme"));
					System.out.println("Branch: " + lineBranchQuery.getObject("branch"));
				}

				System.out.println("");

				System.out.println("Read courses (name (code), credits: grade):");
				while(readCoursesQuery.next()) {
					System.out.println("	" + readCoursesQuery.getObject("courseName") + "("+ readCoursesQuery.getObject("course") + "), " + readCoursesQuery.getObject("credits") + ": " + readCoursesQuery.getObject("grade"));
				}

				System.out.println("");

				System.out.println("Registered courses (name (code), credits: status):");
				while(registeredCoursesQuery.next()) {
					System.out.println("	" + registeredCoursesQuery.getObject("name") + "("+ registeredCoursesQuery.getObject("course") + "), " + registeredCoursesQuery.getObject("credits") + ": " + registeredCoursesQuery.getObject("status"));
				}

				System.out.println("");
				System.out.println("Seminar courses taken: " + queryResultSet.getObject("SeminarCoursesTaken"));
				System.out.println("Math credits taken: " + queryResultSet.getObject("MathematicalCreditsTaken"));
				System.out.println("Research credits taken: " + queryResultSet.getObject("ResearchCreditsTaken"));
				System.out.println("Total credits taken: " + queryResultSet.getObject("TotalCredits"));
				System.out.println("Fulfills the requirements for graduation: " + queryResultSet.getObject("canGraduate"));
				System.out.println("-------------------------------------");
			}
			queryResultSet.close();
			queryResultSet = null;
			statement.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}


	static void registerStudent(Connection conn, String student, String course) {
		PreparedStatement insertStatement;
		insertStatement = conn.prepareStatement("INSERT INTO Registrations VALUES (?, 'X', ?, 'X', 'X', 0)");
		insertStatement.setInt(1, Integer.parseInt(student));
		insertStatement.setString(2, course);
		try {
			insertStatement.execute();
		} catch(SQLException e) {
			e.printStackTrace();
			return;
		} finally {
			insertStatement.close();
		}

		Statement checkStatement = conn.createStatement();
		ResultSet checkResultSet = checkStatement.executeQuery("SELECT * FROM Registrations WHERE ( id = " + student + " AND course = '" + course + "')");
		while (checkResultSet.next()) {
			if (checkResultSet.getObject("status").equals("Registered")) {
				System.out
				.println("You have been succesfully registered to the course.");
			} else if (checkResultSet.getObject("status").equals("Waiting")) {
				System.out.println("The course is unfortunately full, but you have been placed in queue at the position " + checkResultSet.getObject("placeInQueue") + ".");
			}
		}
		checkResultSet.close();
	}


	static void unregisterStudent(Connection conn, String student, String course) {
		int studentID = 0;
		try {
			studentID = Integer.parseInt(student);
		} catch (NumberFormatException e) {
			System.out.println("Student has to be a valid integer.");
			return;
		}
		PreparedStatement unregisterStatement = conn.prepareStatement("DELETE FROM Registrations WHERE (id=? AND course=?)");
		unregisterStatement.setInt(1, studentID);
		unregisterStatement.setString(2, course);
		int deletedRows = unregisterStatement.executeUpdate();
		unregisterStatement.close();

		if(deletedRows = 1) {
			System.out.println("You have been successfully unregistered from the course "	+ course + ".")
		} else {
			System.out.println("You cant unregister from a course you are not registered/waiting to.");
		}
	}
}